﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.Data.SqlServerCe;
using System.Linq;
using System.Text;

namespace SWEN3Application.Classes
{
    class StaffsDBManager
    {
        public static ArrayList GetAllStaffs()
        {
            ArrayList result = new ArrayList();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * from Staffs";
                SqlCeDataReader dr = comm.ExecuteReader();
                DateTime Default = DateTime.Parse("1/1/2001");
                while (dr.Read())
                {
                    Staffs r = new Staffs();

                    r.StaffId = (int)dr["StaffId"];
                    r.StaffName = (String)dr["StaffName"];
                    r.StaffContact = (int)dr["StaffContact"];
                    r.DutyType = (string)dr["DutyType"];
                    r.BankNo = (int)dr["BankNo"];
                    r.DOB = (DateTime)dr["DOB"];
                    r.StaffAddress = (string)dr["StaffAddress"];
                    result.Add(r);


                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public int createStaff(Staffs st)
        {
            SqlCeConnection conn = null;
            int rowinserted = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staffs(StaffId, StaffName, StaffAddress, StaffContact, BankNo, DOB, DutyType) VALUES (@StaffId, @StaffName, @StaffAddress, @StaffContact, @BankNo, @DOB, @DutyType)";

                comm.Parameters.AddWithValue("@StaffId", st.StaffId);
                comm.Parameters.AddWithValue("@StaffName", st.StaffName);
                comm.Parameters.AddWithValue("@StaffAddress", st.StaffAddress);
                comm.Parameters.AddWithValue("@StaffContact", st.StaffContact);
                comm.Parameters.AddWithValue("@BankNo", st.BankNo);
                comm.Parameters.AddWithValue("@DOB", st.DOB);
                comm.Parameters.AddWithValue("@DutyType", st.DutyType);
                rowinserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowinserted;
        }

        public int DeleteStaff(int id)
        {
            SqlCeConnection conn = null;
            int rowsdeleted = 0;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE from Staffs where StaffId=@staffid";

                comm.Parameters.AddWithValue("@staffid", id);
                rowsdeleted = comm.ExecuteNonQuery();

                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return rowsdeleted;
        }

        public Staffs GetStaffsById(int id)
        {
            Staffs r = new Staffs();
            SqlCeConnection conn = null;
            try
            {
                conn = new SqlCeConnection();
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["SWEN3ConnectionString"].ConnectionString;
                conn.Open();
                SqlCeCommand comm = new SqlCeCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Staffs WHERE StaffId=@staffid";
                comm.Parameters.AddWithValue("@staffid", id);
                SqlCeDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    r.StaffId = (int)dr["StaffId"];
                    r.StaffName = (string)dr["StaffName"];
                    r.StaffAddress = (string)dr["StaffAddress"];
                    r.StaffContact = (int)dr["StaffContact"];            
                    r.BankNo = (int)dr["BankNo"];
                    r.DOB = (DateTime)dr["DOB"];
                    r.DutyType = (string)dr["DutyType"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlCeException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return r;
        }
    }
}
